﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects.SqlClient;

namespace University.Workshop.DataAccess
{
    public class DataAccessFeesAndFines
    {
        public static void RegisterFees(Model.FeesAndFines fees)
        {
            WFPEntities.Instance.DiscardChanges();
            WFPEntities.Instance.FeesAndFines.Add(fees);
            WFPEntities.Instance.SaveChanges();
        }

        public static void ModifyFees(Model.FeesAndFines fees)
        {
            WFPEntities.Instance.DiscardChanges();
            var fe = (from f in WFPEntities.Instance.FeesAndFines
                      where f.id == fees.id
                      select f).First();
            fe.Charge = fees.Charge;
            fe.NewConection = fees.NewConection;
            fe.Reconnection = fees.Reconnection;
            fe.BaseRate = fees.BaseRate;
            fe.NewMember = fees.NewMember;
            fe.MonthlyContribution = fees.MonthlyContribution;
            fe.NonPayment = fees.NonPayment;
            fe.MeterViolation = fees.MeterViolation;
            fe.EffectiveDate = fees.EffectiveDate;
            WFPEntities.Instance.SaveChanges();
        }

        public static ICollection<Model.FeesAndFines> GetLastInserted()
        {
           return(from f in WFPEntities.Instance.FeesAndFines
                  orderby f.id descending
                  select f).ToList();
        }

        public static ICollection<Model.FeesAndFines> SearchFeesByParameter(Model.FeesAndFines feesRelative)
        {
            WFPEntities.Instance.DiscardChanges();
            var charge = feesRelative.Charge == 0? "" : feesRelative.Charge.ToString();
            var nonPayment = feesRelative.NonPayment == 0? "" : feesRelative.NonPayment.ToString();
            if (feesRelative.EffectiveDate != null)
            {
                var q = from f in WFPEntities.Instance.FeesAndFines
                        where (SqlFunctions.StringConvert( f.Charge).StartsWith(charge) &&
                                SqlFunctions.StringConvert(f.NonPayment).StartsWith(nonPayment) &&
                                f.EffectiveDate.Value >= feesRelative.EffectiveDate.Value)
                        select f;
                return q.ToList();
            }
            else
            {
                var q = from f in WFPEntities.Instance.FeesAndFines
                        where (SqlFunctions.StringConvert(f.Charge).StartsWith(charge) &&
                                SqlFunctions.StringConvert(f.NonPayment).StartsWith(nonPayment))
                        select f;
                return q.ToList();
            }
        }

        public static ICollection<Model.FeesAndFines> GetCurrentFeesAndFines()
        {
            var p = (from f in WFPEntities.Instance.FeesAndFines
                     where f.EffectiveDate <= DateTime.Now
                     orderby f.EffectiveDate descending
                     select f).ToList();
            return p;
        }

        internal static Model.FeesAndFines GetFeesVigentInDate(DateTime dateTime)
        {
            var p = (from f in WFPEntities.Instance.FeesAndFines
                     where f.EffectiveDate <= dateTime
                     orderby f.EffectiveDate descending
                     select f).ToList();
            return p.Count==0?null:p.Last();
        }

        public static bool ExistAnyFeesAndFinesRegistered()
        {
            var feesAndFines = (from ff in WFPEntities.Instance.FeesAndFines
                                select ff).ToList ();

            return (feesAndFines.Count > 0 ? true : false);
        }
    }
}
